﻿using STHPMS1.DAL.D_Core;
using STHPMS1.ICore.I_DAL.Repository;
using STHPMS1.ICore.I_Entity.Stmpms1;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ServiceStack.OrmLite;
using System;
using System.Text;

namespace STHPMS1.DAL.D_Repository
{
    public class S_TrfvolsurveyRepository : DalBase, IS_TrfvolsurveyRepository
    {
        /// <summary>
        /// 一个应用区间同一个观测日期下有可能存在不同观测点数据，现只采用一个观测点数据
        /// 一个应用区间每年也只能使用一个数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="applyIds"></param>
        /// <returns></returns>
        public IEnumerable<T> GetTrfvolsurveyByApply<T>(string applyIds)
               where T : class, IS_Trfvolsurvey
        {
            string applyIdWhere = "";
            if (!string.IsNullOrEmpty(applyIds)) applyIdWhere =string.Format(" and applyid in( {0} )",applyIds);
            string sql = string.Format(@"select * from (select * , row_number() over(partition by APPLYID,year(OBVTM) order by OBVTM desc) as sequence 
from S_TRFVOLSURVEY ) a where sequence =1 {0} ORDER BY obvtm asc;", applyIdWhere);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }


        public IEnumerable<T> GetTrfvolsurveyBySeg<T>(string rdwayid,double beginmp,double endmp,int direct,double? ver)
             where T : class, IS_Trfvolsurvey
        {
         string sql = string.Format(@"select b.*
from S_APPLY  a 
join S_TRFVOLSURVEY b
on a.APPLYID==b.APPLYID
where a.APPRDID='{0}' and 
	a.APPBEGINMP<={1} and 
	a.APPENDMP>=2} and 
	a.DIRECTION_DICTID={3} and
	b.HIS_VERSION={4};", rdwayid,beginmp,endmp,direct,ver);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }

        public DataTable GetTrfvolsurveyByRoad(string rdwayid ,int? direct,DateTime? checkDate)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select b.Obvtm,b.Statname,b.His_Version,b.Applyid,b.Truck_S,b.Truck_M,b.Truck_L,b.Truck_O,b.Car_S,b.Car_L,b.Artveh,b.Contcar,b.Tractor from S_APPLY a
JOIN S_TRFVOLSURVEY b
on a.APPLYID=b.APPLYID where 1=1");
            if (!string.IsNullOrEmpty(rdwayid))
            {
                sql.AppendFormat(" and a.APPRDID='{0}' ", rdwayid);                
            }

            if (direct != null)
            {
                sql.AppendFormat(" and a.DIRECTION_DICTID={0} ", direct);              
            }

            if (checkDate != null)
            {
                sql.AppendFormat(" and b.OBVTM='{0}' ", ((DateTime)checkDate).ToString("yyyy-MM-dd HH:mm:ss.fff"));                 
            }

            return Adapter.DataAdapter.FillDataTable(sql.ToString());
        }
    }
}
